VERSION 5.00
Begin VB.Form frmBackup 
   Caption         =   "Backup a table"
   ClientHeight    =   4740
   ClientLeft      =   60
   ClientTop       =   450
   ClientWidth     =   8640
   LinkTopic       =   "Form1"
   ScaleHeight     =   4740
   ScaleWidth      =   8640
   StartUpPosition =   3  'Windows Default
   Begin VB.ListBox listHistory 
      Height          =   3570
      Left            =   4320
      TabIndex        =   2
      Top             =   720
      Width           =   4095
   End
   Begin VB.CommandButton cmdBackup 
      Caption         =   "Backup"
      Height          =   375
      Left            =   2760
      TabIndex        =   1
      Top             =   840
      Width           =   1335
   End
   Begin VB.ListBox lstAll 
      Height          =   3570
      Left            =   240
      TabIndex        =   0
      Top             =   720
      Width           =   2295
   End
   Begin VB.Label Label2 
      Caption         =   "History:"
      Height          =   375
      Left            =   4320
      TabIndex        =   4
      Top             =   240
      Width           =   1695
   End
   Begin VB.Label Label1 
      Caption         =   "All Tables:"
      Height          =   375
      Left            =   360
      TabIndex        =   3
      Top             =   240
      Width           =   1695
   End
End
Attribute VB_Name = "frmBackup"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim sql As String
'backup a single table to local pc
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

'load all CSV file from filesystem
Private Sub Load_CSV()
    Dim FileStr As String
    'list all files to local disk
    Call Shell("cmd /c dir /a-d /b /s *.csv >tmpCSVList.txt", vbHide)
    DoEvents
    DoEvents
    DoEvents
    DoEvents
    DoEvents
    DoEvents
    'read data to memory
    Open "tmpCSVList.txt" For Input As #1
    listHistory.Clear
    Do While Not EOF(1)
        Dim tmp As String
        Line Input #1, tmp
        FileStr = Right$(tmp, InStr(1, StrReverse(tmp), "\") - 1)
        listHistory.AddItem FileStr
        DoEvents
    Loop
    Close #1
End Sub
    
   
'backup a single table to CSV file mode
Private Sub cmdBackup_Click()
    Dim iFields As Integer
    Dim sOneLine, sFileName As String
    Dim i As Integer
    Dim iIndex As Long
    iIndex = 0
    frmSplash.Show
    DoEvents
    Call Cn2Oversea(cn)
    sql = "SELECT * from `" & lstAll.Text & "`"
    Call CloseRecordSet(rs)
    frmSplash.lblStatus.Caption = "It is connecting and fetching data from database..."
    DoEvents
    OperateDatabase rs, cn, sql
    iFields = rs.Fields.Count
    sFileName = lstAll.Text & Year(Date) & Month(Date) & Day(Date) & Hour(Time) & Minute(Time) & ".csv"
    Open BACKUP_FOLDER & sFileName For Append As #2
    
    While Not rs.EOF
        sOneLine = ""
        For i = 0 To iFields - 2
            sOneLine = sOneLine & Chr(34) & rs.Fields(i).value & Chr(34) & ","
        Next i
        'no need ',' for last record
        sOneLine = sOneLine & Chr(34) & Format(rs.Fields(i).value, "yyyy-MM-dd HH:mm:ss") & Chr(34)
        Print #2, sOneLine
        rs.MoveNext
        frmSplash.lblStatus.Caption = "It is saving " & iIndex & " of " & rs.Fields.Count & " ..."
        DoEvents
    Wend
    Close #2
    DoEvents
    Unload frmSplash
    cn.Close
    MsgBox "Backup Done!", vbOKOnly, SOFT_TITLE
    
    Load_CSV
End Sub

Private Sub Form_Load()
    Call Cn2System(cn)
    sql = "select table_name from tables where table_schema='overseac_cslibary'"
    Call CloseRecordSet(rs)
    OperateDatabase rs, cn, sql
    While Not rs.EOF
        lstAll.AddItem rs.Fields(0).value
        rs.MoveNext
    Wend
    'close the system database
    cn.Close
    
    Load_CSV
End Sub

Private Sub CloseRecordSet(ByRef rs As ADODB.Recordset)
On Error GoTo NoErr
        rs.Close
NoErr:
End Sub

Private Sub lstAll_DblClick()
    cmdBackup_Click
End Sub
